package cn.gzmli.dtt.common.workflow.service;


import cn.gzmli.dtt.common.config.cache.Config;
import cn.gzmli.dtt.common.entity.Department;
import cn.gzmli.dtt.common.entity.PageInfo;
import cn.gzmli.dtt.common.entity.Result;
import cn.gzmli.dtt.common.entity.User;
import cn.gzmli.dtt.common.service.UserService;
import cn.gzmli.dtt.common.utils.BaseUtil;
import cn.gzmli.dtt.common.workflow.controller.vo.BusinessEntityInfo;
import cn.gzmli.dtt.common.workflow.controller.vo.NodeForwordInfo;
import cn.gzmli.dtt.common.workflow.controller.vo.WorkFlow_VO;
import cn.gzmli.dtt.common.workflow.dao.FlowNodeDao;
import cn.gzmli.dtt.common.workflow.dao.TaskLogDao;
import cn.gzmli.dtt.common.workflow.dao.WorkFlowDao;
import cn.gzmli.dtt.common.workflow.entity.FlowNode;
import cn.gzmli.dtt.common.workflow.entity.TaskLog;
import cn.gzmli.dtt.common.workflow.entity.WorkFlow;
import cn.gzmli.dtt.common.dao.DepartmentDao;
import com.xiaoleilu.hutool.util.StrUtil;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import org.codehaus.jackson.JsonParser;
import org.codehaus.jackson.map.ObjectMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.transaction.SystemException;
import java.io.File;
import java.io.FileInputStream;
import java.util.*;

@Service
@Transactional
public class WorkFlowService {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private WorkFlowDao workFlowDao;
    @Autowired
    private FlowNodeDao flowNodeDao;
    @Autowired
    private TaskLogDao taskLogDao;
    @Autowired
    private DepartmentDao departmentDao;
    @Autowired
    private UserService userService;

    private static final String WORKFLOW_CONF_PATH = WorkFlowService.class.getResource("/").getPath() + File.separator + "config" + File.separator + "workFlow.conf";

    public JdbcTemplate getJdbcTemplate(){
        return jdbcTemplate;
    }
    /**
     * 更新或新增流程
     *
     * @param workFlow
     * @throws Exception
     */
    @Transactional(propagation = Propagation.REQUIRED)
    public void updateOrInsertWorkFlow(String workFlowID, WorkFlow_VO workFlow, String removeNodeIDS) throws Exception {

        String delFlowNodeSql = String.format("DELETE FROM flowNode WHERE id IN (%s) AND workFlowID='%s'",
                BaseUtil.toInCondNotSprit(removeNodeIDS), workFlowID);
        String delNodeOrgRoleSql = String.format("DELETE nodeorgrole FROM flowNode INNER JOIN nodeorgrole ON (flownode.id=nodeorgrole.flownodeID)  "
                + "WHERE flownode.id IN (%s) AND flownode.workFlowID='%s'", BaseUtil.toInCondNotSprit(removeNodeIDS), workFlowID);
        String delNodeUserSql = String.format("DELETE nodeuser FROM flowNode INNER JOIN nodeuser ON (flownode.id=nodeuser.flownodeID)  "
                + "WHERE flownode.id IN (%s) AND flownode.workFlowID='%s'", BaseUtil.toInCondNotSprit(removeNodeIDS), workFlowID);

        jdbcTemplate.execute(delFlowNodeSql);
        jdbcTemplate.execute(delNodeOrgRoleSql);
        jdbcTemplate.execute(delNodeUserSql);

        String existSql = String.format("SELECT COUNT(*) FROM WorkFlow workFlow WHERE id='%s'", workFlow.getId());
        boolean isExist = jdbcTemplate.queryForObject(existSql, Integer.class) > 0 ? true : false;

        //判断是新增流程还是更新流程
        if (!isExist) {

            String insertWorkFlow = String.format("INSERT INTO WorkFlow(id,name,flowViewData,descript) "
                    + "VALUES('%s','%s','%s','%s')", workFlow.getId(), workFlow.getName(), workFlow.getFlowViewData(), workFlow.getDescript());
            jdbcTemplate.execute(insertWorkFlow);
        } else {
            String updateWorkFlow = String.format("UPDATE WorkFlow SET name='%s',flowViewData='%s',descript='%s' WHERE id='%s'",
                    workFlow.getName(), workFlow.getFlowViewData(), workFlow.getDescript(), workFlow.getId());
            jdbcTemplate.execute(updateWorkFlow);
        }
        //循环新增或更新节点
        if (workFlow.getNodes() != null) {
            List<FlowNode> nodes = workFlow.getNodes();
            for (int i = 0; i < nodes.size(); i++) {

                JSONObject json = JSONObject.fromObject(nodes.get(i));
                FlowNode flowNode = (FlowNode) JSONObject.toBean(json, FlowNode.class);
                Integer nodeID = flowNode.getId();

             /*   String sql = String.format("SELECT COUNT(*) FROM FlowNode WHERE workFlowID='%s' AND nodeOrder='%s' ",workFlowID, flowNode.getNodeOrder());
                boolean isExistsNode = jdbcTemplate.queryForObject(sql, Integer.class) > 0 ? true : false;*/
                //如果节点ID为空，则证明该节点为新增节点
                if (flowNode.getId() == null ||(flowNode!=null&&flowNode.getId()==0)|| (flowNode!=null&&flowNode.getId().equals(""))) {

                    //flowNode.setId(UUID.randomUUID().toString().replaceAll("\\-","").toLowerCase());//改自增获取
                    String insertNode = String.format("INSERT INTO FlowNode(workFlowID,name,"
                                    + "nodeType,objectScope,orgScope,nodeOrder,performType,"
                                    + "fromRect,toRect,width,height,x,y,textX,textY,isAutoExe,"
                                    + "partInMode,descript) "
                                    + "VALUES('%s','%s',%d,%d,%d,%d,%d,'%s','%s',%d,%d,%d,%d,%d,%d,%d,%d,'%s')",
                            workFlowID, flowNode.getName(),
                            flowNode.getNodeType(), flowNode.getObjectScope(), flowNode.getOrgScope(),
                            flowNode.getNodeOrder(), flowNode.getPerformType(), flowNode.getFromRect(),
                            flowNode.getToRect(), flowNode.getWidth(), flowNode.getHeight(), flowNode.getX(),
                            flowNode.getY(), flowNode.getTextX(), flowNode.getTextY(), flowNode.getIsAutoExe(),
                            flowNode.getPartInMode(), flowNode.getDescript());
                    jdbcTemplate.execute(insertNode);
                    String getIDSql = "SELECT LAST_INSERT_ID() FROM DUAL";
                    flowNode.setId(jdbcTemplate.queryForObject(getIDSql, Integer.class));
                    String insertObjectSql = null;

                    //如果参与对象范围为机构
                    if (flowNode.getObjectScope() == 0 && flowNode.getOrgIds() != null && !flowNode.getOrgIds().equals("")) {
                        String inOrgIDSCond = BaseUtil.toInCondNotSprit(flowNode.getOrgIds());
                        insertObjectSql = String.format("INSERT INTO NodeOrgRole(flowNodeId,orgID) "
                                + " SELECT '%s',id FROM Department WHERE id IN (%s)", flowNode.getId(), inOrgIDSCond);
                        jdbcTemplate.execute(insertObjectSql);
                        //如果参与对象范围为机构角色
                    } else if (flowNode.getObjectScope() == 1 &&
                            flowNode.getOrgIds() != null && !flowNode.getOrgIds().equals("") && flowNode.getRoleIds() != null && !flowNode.getRoleIds().equals("")) {
                        String inOrgIDSCond = BaseUtil.toInCondNotSprit(flowNode.getOrgIds());
                        String inRoleIDSCond = BaseUtil.toInCondNotSprit(flowNode.getRoleIds());
                        insertObjectSql = String.format("INSERT INTO NodeOrgRole(flowNodeId,orgID,roleID) "
                                        + " SELECT '%s',org.id,role.id FROM Department AS org,Role AS role WHERE org.id IN (%s) AND role.id IN (%s)",
                                flowNode.getId(), inOrgIDSCond, inRoleIDSCond);
                        jdbcTemplate.execute(insertObjectSql);

                    }
                    //如果参与对象范围为机构角色并且机构来源于当前用户机构或者父机构
                    else if (flowNode.getObjectScope() == 1 && flowNode.getOrgScope() > 0 && flowNode.getRoleIds() != null && !flowNode.getRoleIds().equals("")) {
                        String inOrgIDSCond = BaseUtil.toInCondNotSprit(flowNode.getOrgIds());
                        String inRoleIDSCond = BaseUtil.toInCondNotSprit(flowNode.getRoleIds());
                        insertObjectSql = String.format("INSERT INTO NodeOrgRole(flowNodeId,roleID) "
                                        + " SELECT '%s',role.id FROM Role AS role WHERE  role.id IN (%s)",
                                flowNode.getId(), inRoleIDSCond);
                        jdbcTemplate.execute(insertObjectSql);

                    }
                    //如果参与对象范围为用户
                    else if (flowNode.getObjectScope() == 2 && flowNode.getUserIds() != null && !flowNode.getUserIds().equals("")) {
                        String inUserIDSCond = BaseUtil.toInCondNotSprit(flowNode.getUserIds());
                        String inOrgIDSCond = BaseUtil.toInCondNotSprit(flowNode.getOrgIds());
                        String inRoleIDSCond = BaseUtil.toInCondNotSprit(flowNode.getRoleIds());
                        insertObjectSql = String.format("INSERT INTO NodeOrgRole(flowNodeId,orgID,roleID) "
                                        + " SELECT '%s',org.id,role.id FROM Department AS org,Role AS role WHERE org.id IN (%s) AND role.id IN (%s)",
                                flowNode.getId(), inOrgIDSCond, inRoleIDSCond);
                        jdbcTemplate.execute(insertObjectSql);
                        insertObjectSql = String.format("INSERT INTO NodeUser(flowNodeId,userID) "
                                + " SELECT '%s',id FROM User WHERE id IN (%s)", flowNode.getId(), inUserIDSCond);
                        jdbcTemplate.execute(insertObjectSql);
                    }
                    //否则为更新节点
                } else {
                    String updateNode = String.format("UPDATE FlowNode SET name='%s',"
                                    + "nodeType=%d,objectScope=%d,orgScope=%d,nodeOrder=%d,performType=%d,"
                                    + "fromRect='%s',toRect='%s',width=%d,height=%d,x=%d,y=%d,"
                                    + "textX=%d,textY=%d,isAutoExe=%d,partInMode=%d,descript='%s' "
                                    + "WHERE id='%s' AND workFlowID='%s' ",
                            flowNode.getName(),
                            flowNode.getNodeType(), flowNode.getObjectScope(), flowNode.getOrgScope(),
                            flowNode.getNodeOrder(), flowNode.getPerformType(), flowNode.getFromRect(),
                            flowNode.getToRect(), flowNode.getWidth(), flowNode.getHeight(), flowNode.getX(),
                            flowNode.getY(), flowNode.getTextX(), flowNode.getTextY(), flowNode.getIsAutoExe(),
                            flowNode.getPartInMode(), flowNode.getDescript(), flowNode.getId(),workFlowID);
                    jdbcTemplate.execute(updateNode);
                    String insertObjectSql = null;

                    //删除原有的节点参与对象关联
                    String deleteSql = String.format("DELETE nor FROM NodeOrgRole AS nor,FlowNode fn "
                            + "WHERE nor.flowNodeId=fn.id AND fn.nodeOrder='%s' AND fn.workFlowID='%s'", flowNode.getNodeOrder(),workFlowID);
                    jdbcTemplate.execute(deleteSql);
				/*deleteSql=String.format("DELETE nor FROM NodeOrgRole AS nor,FlowNode fn "
						+ "WHERE nor.flowNodeId=fn.id AND fn.nodeOrder='%s' AND fn.workFlowID='%s'",flowNode.getNodeOrder(),flowNode.getWorkFlowId());
				jdbcTemplate.execute(deleteSql);*/
                    deleteSql = String.format("DELETE nu FROM NodeUser AS nu,FlowNode AS fn WHERE  nu.flowNodeId=fn.id AND fn.nodeOrder='%s' AND fn.workFlowID='%s'", flowNode.getNodeOrder(), workFlowID);
                    jdbcTemplate.execute(deleteSql);

                    //如果参与对象范围为机构,插入新的参与对象
                    if (flowNode.getObjectScope() == 0 && flowNode.getOrgIds() != null && !flowNode.getOrgIds().equals("")) {
                        String inOrgIDSCond = BaseUtil.toInCondNotSprit(flowNode.getOrgIds());
                        insertObjectSql = String.format("INSERT INTO NodeOrgRole(flowNodeId,orgID) "
                                        + " SELECT flowNode.id,org.id FROM Department AS org,FlowNode AS flowNode "
                                        + "WHERE org.id IN (%s) AND flowNode.nodeOrder='%s' AND flowNode.workFlowID='%s'",
                                inOrgIDSCond, flowNode.getNodeOrder(),workFlowID);

                        jdbcTemplate.execute(insertObjectSql);
                        //如果参与对象范围为机构角色
                    } else if (flowNode.getObjectScope() == 1 &&
                            flowNode.getOrgIds() != null && !flowNode.getOrgIds().equals("") && flowNode.getRoleIds() != null && !flowNode.getRoleIds().equals("")) {
                        String inOrgIDSCond = BaseUtil.toInCondNotSprit(flowNode.getOrgIds());
                        String inRoleIDSCond = BaseUtil.toInCondNotSprit(flowNode.getRoleIds());
                        insertObjectSql = String.format("INSERT INTO NodeOrgRole(flowNodeId,orgID,roleID) "
                                        + " SELECT flowNode.id,org.id,role.id FROM Department AS org,Role AS role,FlowNode AS flowNode WHERE org.id IN (%s) AND role.id IN (%s) "
                                        + "AND flowNode.nodeOrder='%s' AND flowNode.workFlowID='%s'",
                                inOrgIDSCond, inRoleIDSCond, flowNode.getNodeOrder(), workFlowID);

                        jdbcTemplate.execute(insertObjectSql);

                    }//如果参与对象范围为机构角色并且机构来源于当前用户机构或者父机构
                    else if (flowNode.getObjectScope() == 1 && flowNode.getOrgScope() > 0 && flowNode.getRoleIds() != null && !flowNode.getRoleIds().equals("")) {
                        String inOrgIDSCond = BaseUtil.toInCondNotSprit(flowNode.getOrgIds());
                        String inRoleIDSCond = BaseUtil.toInCondNotSprit(flowNode.getRoleIds());
                        insertObjectSql = String.format("INSERT INTO NodeOrgRole(flowNodeId,roleID) "
                                        + " SELECT flowNode.id,role.id FROM FlowNode flowNode,Role role WHERE flowNode.nodeOrder='%s' AND flowNode.workFlowID='%s' AND  role.id IN (%s)",
                                flowNode.getNodeOrder(), workFlowID, inRoleIDSCond);
                        jdbcTemplate.execute(insertObjectSql);
                    }


                    //如果参与对象范围为用户
                    if (flowNode.getObjectScope() == 2 && flowNode.getUserIds() != null && !flowNode.getUserIds().equals("")) {
                        String inUserIDSCond = BaseUtil.toInCondNotSprit(flowNode.getUserIds());
                        String inOrgIDSCond = BaseUtil.toInCondNotSprit(flowNode.getOrgIds());
                        String inRoleIDSCond = BaseUtil.toInCondNotSprit(flowNode.getRoleIds());
                        insertObjectSql = String.format("INSERT INTO NodeOrgRole(flowNodeId,orgID,roleID) "
                                        + " SELECT '%s',org.id,role.id FROM Department AS org,Role AS role WHERE org.id IN (%s) AND role.id IN (%s)",
                                flowNode.getId(), inOrgIDSCond, inRoleIDSCond);
                        jdbcTemplate.execute(insertObjectSql);
                        insertObjectSql = String.format("INSERT INTO NodeUser(flowNodeId,userID) "
                                + " SELECT fn.id,u.id FROM User AS u,FlowNode AS fn WHERE u.id IN (%s) AND fn.nodeOrder='%s' AND fn.workFlowID='%s' ", inUserIDSCond, flowNode.getNodeOrder(), workFlowID);
                        jdbcTemplate.execute(insertObjectSql);
                    }

                }
            }

        }

    }

    /**
     * 根据流程ID查询流程信息
     *
     * @param flowID
     * @return
     * @throws Exception
     */
    public WorkFlow queryWorkFlowByID(String flowID) throws Exception {
        Map<String, String> condition = new HashMap<String, String>();
        condition.put("id", " = '" + flowID + "'");
        return workFlowDao.queryOnlyEntity(condition);
    }

    /**
     * 条件查询流程信息
     *
     * @param condition
     * @param pageInfo
     * @return
     * @throws Exception
     */
    public List<WorkFlow> queryWorkFlow(Map<String, String> condition, PageInfo pageInfo) throws Exception {
        return workFlowDao.queryEntityByPage(condition, pageInfo);
    }

    /**
     * 根据流程节点id询流程信息
     * @param flowNodeId
     * @return
     * @throws Exception
     */
    public Map<String,Object> queryWorkFlowByFlowNodeId(Integer flowNodeId) throws Exception {
        String sql="SELECT wf.id workFlowId,wf.name workFlowName FROM workflow wf INNER JOIN flownode fn ON fn.workFlowId=wf.id WHERE fn.id=?";
        Object[] args=new Object[]{flowNodeId};
        return workFlowDao.getJdbcTemplate().queryForMap(sql,args);
    }

    /**
     * 查询所有工作流
     *
     * @return
     * @throws Exception
     */
    public List<WorkFlow> queryWorkFlow() throws Exception {
        return workFlowDao.queryEntityAll("");
    }

    /**
     * 获取起始节点
     *
     * @return
     * @throws Exception
     */
    public Map<String, Object> queryBeginNode(String workFlowID) throws Exception {
        String sql = String.format("SELECT * FROM FlowNode fn WHERE workFlowID='%s' AND nodeType=0",
                workFlowID);
        return workFlowDao.getJdbcTemplate().queryForMap(sql);

    }

    public Map<String, Object> taskProc(HttpServletRequest request,
                                        HttpServletResponse response,
                                        String taskLogID,
                                        String curFlowNodeID,
                                        Integer status,
                                        String flowNodeID,
                                        Integer formID,
                                        Integer curVersion,
                                        String userIDS,
                                        Map<String, Object> paramsMap) throws Exception {

        File flowConf = new File(WORKFLOW_CONF_PATH);
        FileInputStream fis = new FileInputStream(flowConf);
        byte[] buffer = new byte[fis.available()];
        fis.read(buffer);
        String workFlowJson = new String(buffer, "UTF-8");
        workFlowJson = workFlowJson.replaceAll("\\/\\/[^\\n]*", "");
        ObjectMapper objectMapper = new ObjectMapper();
        objectMapper.configure(JsonParser.Feature.ALLOW_UNQUOTED_CONTROL_CHARS, true);
        Map<String, Map<String, Object>> workFlowConf = objectMapper.readValue(
                workFlowJson, Map.class);
        Map<String, Object> result = new HashMap<String, Object>();
        Map<String, String> condition = new HashMap<String, String>();
        condition.put("id", "=" + taskLogID);
        TaskLog taskLog = taskLogDao.queryOnlyEntity(condition);

        //获取当前执行任务流程节点对象
        FlowNode flowNode = taskLog.getFlowNode();

        Integer logID = null;

        //验证判断消息
        String msg = "";
        User user = Config.getUser(request);
        //获取流程信息
        Map<String, Object> workFlow = workFlowConf.get(flowNode.getWorkFlowId());
        //获取节点信息
        Map<String, Object> nodeInfo = (Map<String, Object>) workFlow.get(taskLog.getFlowNode().getId());
        //获取业务实体定义信息
        Map<String, Object> bEntityInfo = (Map<String, Object>) nodeInfo.get("bEntityInfo");

        //获取当前流程节点信息
        //FlowNode curFlowNode=workFlowService.queryFlowNodeByID(curFlowNodeID);

        //节点流转事件
        Map<String, Object> taskProc = (Map<String, Object>) nodeInfo.get("taskProc");
        //节点流转操作
        Map<String, Object> bStatus = (Map<String, Object>) taskProc.get("bStatus");

        //判读该节点业务状态是否定义
        if (bStatus != null && !(boolean) bStatus.get("isDisable")) {
            //获取定义的业务状态键集合
            Set<String> bStatusKeys = bStatus.keySet();
            //用于判断当前业务状态是否在节点配置中定义
            boolean isDefined = false;
            Integer version = null;
            String userIDSInCond = BaseUtil.toInCondNotSprit(userIDS);
            //获取定义的业务状态键值
            for (String key : bStatusKeys) {

                //获取业务实体数据
                Map<String, Object> bd = this.queryBD(bEntityInfo.get("bEntityName").toString(),
                        bEntityInfo.get("idKey").toString(), taskLog.getFormId().toString());

                if (key.indexOf(bd.get(bEntityInfo.get("bStatusKey").toString()).toString()) != -1) {
                    isDefined = true;

                    //获取当前业务状态下的执行定义对象
                    Map<String, Object> bStatusConf = (Map<String, Object>) bStatus.get(key);
                    //获取当前状态下的任务状态定义对象
                    Map<String, Object> tStatusConf = (Map<String, Object>) bStatusConf.get("status");
                    //获取当前提交的任务状态下执行列表
                    List<Map<String, Object>> execList = (List<Map<String, Object>>) tStatusConf.get(status.toString());


                    Map<String, String> extendParams = new HashMap<String, String>();
                    extendParams.put("taskLogID", taskLogID.toString());
                    extendParams.put("formID", taskLog.getFormId().toString());
                    extendParams.put("flowNodeID", taskLog.getFlowNode().getId().toString());
				/*extendParams.put("userIDS",userIDS);
				extendParams.put("processerIDS",userIDS);
				extendParams.put("forwarderID",curUser.getId().toString());*/
                    extendParams.put("processerID", user.getId().toString());
                    if (curVersion != null) {
                        version = curVersion;
                    } else {
                        version = this.queryCurVersion("TaskLog", taskLog.getFlowNode().getId().toString(), taskLog.getFormId());
                    }
                    extendParams.put("version", version.toString());

                    for (Map<String, Object> exec : execList) {
                        String sqlExpress = exec.get("sqlExpress").toString();
                        if (sqlExpress.indexOf("${") != -1) {
                            String sql = this.procSqlExpressParam(sqlExpress, paramsMap, extendParams);
                            exec.put("sql", sql);
                            System.out.println(sql);
                        }
                    }

                    this.executeTaskSql(execList, taskLog.getFormId(), taskLog.getFlowNode().getId().toString(), version);
                    result.put("state", 1);
                    //返回处理结束后的任务记录
                    result.put("taskLog", taskLogDao.queryOnlyEntity(condition));
                    result.put("msg", "节点任务处理完成！");
                }
            }
        }

        taskLog = result.get("taskLog") == null ? null : (TaskLog) result.get("taskLog");
        if (taskLog != null) {


            final User forwordor_F = Config.getUser(request);
            final Integer formID_F = taskLog.getFormId();
            final String flowNodeID_F = taskLog.getFlowNode().getId().toString();
            final Integer taskLogID_F = taskLog.getId();
            final Integer status_F = status;
            new Thread(new Runnable() {
                @Override
                public void run() {
                    try {
                        //////////////////////////////////////推送
                        //FlowNotifPush.sendMessage(null,flowNodeID_F,formID_F.toString(),1,status_F,null);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }).start();
        }

        return result;


    }

    /**
     * 自定义流程sql表达式处理
     *
     * @param sqlExpress
     * @param paramsMap
     * @return
     */
    private String procSqlExpressParam(String sqlExpress, Map<String, Object> paramsMap, Map<String, String> extendParams) throws Exception {

        String param = "";
        String sql = new String(sqlExpress);
        //逐字符串检索
        for (int i = 0; i < sqlExpress.length(); i++) {
            char c = sqlExpress.charAt(i);

            if (c == '$' && sqlExpress.charAt(i + 1) == '{') {
                param = "";
                //跳过表达式符号
                i = i + 2;
                for (; i < sqlExpress.length(); i++) {
                    //判读表达式结尾
                    if (sqlExpress.charAt(i) == '}'){
                        break;
                    }
                    //获取参数名称
                    param += sqlExpress.charAt(i);
                }

                if (param.equals("x_Add_TableName")) {
                    StringBuffer x_Add_Fields = new StringBuffer();
                    StringBuffer x_Add_Values = new StringBuffer();
                    String x_Add_TableName = paramsMap.get("x_Add_TableName") != null ? paramsMap.get("x_Add_TableName").toString() : "";
                    for (String str : paramsMap.keySet()) {
                        if (str.indexOf("x_Add") == 0 && !str.equals("x_Add_TableName")) {
                            x_Add_Fields.append(str.replaceAll("x_Add_", "") + ",");
                            x_Add_Values.append("'" + paramsMap.get(str) + "',");
                        }
                    }

                    if (x_Add_Fields.indexOf(",") != -1) {
                        x_Add_Fields.delete(x_Add_Fields.length() - 1, x_Add_Fields.length());

                    }

                    if (x_Add_Values.indexOf(",") != -1) {
                        x_Add_Values.delete(x_Add_Values.length() - 1, x_Add_Values.length());
                    }
                    System.out.println(x_Add_Fields + "," + x_Add_Values);
                    sql = sql.replaceAll("\\$\\{\\s*x_Add_Fields\\s*\\}", x_Add_Fields.toString());
                    sql = sql.replaceAll("\\$\\{\\s*x_Add_Values\\s*\\}", x_Add_Values.toString());
                    sql = sql.replaceAll("\\$\\{\\s*x_Add_TableName\\s*\\}", x_Add_TableName);
                    continue;
                }
                //获取参数值
                //String value=req.getParameter(param);
                String value = paramsMap.get(param.toString()) != null ? paramsMap.get(param.toString()).toString() : null;
                //在扩展参数里找
                if (value == null) {
                    value = extendParams.get(param);
                }
                //空值和空字符串处理
                if (value != null && !value.equals("")) {
                    sql = sql.replaceAll("\\$\\{\\s*" + param + "\\s*\\}", value);
                } else {
                    //去单引号，填充空值
                    if (sqlExpress.matches("[\\w\\W]*\\'+\\$\\{\\s*" + param + "\\s*\\}\\'+[\\w\\W]*")) {
                        sql = sql.replaceAll("\\'+\\$\\{\\s*" + param + "\\s*\\}\\'+", "NULL");
                    } else {
                        sql = sql.replaceAll("\\$\\{\\s*" + param + "\\s*\\}", "NULL");
                    }
                }
            }

        }
        return sql;
    }

    /**
     * 查询当前节点的所有下一级流程节点
     *
     * @param flowNodeID
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryNextFlowNodes(String flowNodeID, String workFlowID) throws Exception {
        String sql = String.format("CALL proc_QueryNextNodes('%s','%s')", flowNodeID, workFlowID);
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * 根据节点ID获取当前节点
     *
     * @param flowNodeID
     * @return
     * @throws Exception
     */
    public FlowNode queryFlowNodeByID(String flowNodeID) throws Exception {
        Map<String, String> condition = new HashMap<String, String>();
        condition.put("id", " ='" + flowNodeID + "'");
        return flowNodeDao.queryOnlyEntity(condition);
    }

    /**
     * 根据节点ID获取节点信息
     *
     * @param flowNodeIDS
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryFlowNodesByID(String flowNodeIDS) throws Exception {
        String sql = String.format("SELECT * FROM FlowNode WHERE id IN(%s)", flowNodeIDS);
        return jdbcTemplate.queryForList(sql);
    }

    /*-------------------------------审核记录操作----------------------------------*/

    /**
     * 关联用户插入审核记录
     *
     * @param flowNodeID
     * @param formDataID
     * @param userIDS
     * @throws Exception
     */
    public int insertAuditLog(String workflowID, String flowNodeID, Integer formDataID, Integer version, String userIDS, Integer forwordorID, BusinessEntityInfo bEntityInfo, NodeForwordInfo nodeForwordInfo) throws Exception {
        String userIDSInCond = BaseUtil.toInCondNotSprit(userIDS);
        String sql = String.format("INSERT INTO AuditLog (auditerID,workflowID,flowNodeId,formID,status,forworderID,version) "
                        + "SELECT u.id,'%s','%s',%d,0,%d,%d FROM User u WHERE u.id IN (%s)",
                workflowID, flowNodeID, formDataID, forwordorID, version, userIDSInCond);
        if (bEntityInfo != null) {
            updateBDStatus(bEntityInfo);
        }
        if (nodeForwordInfo != null) {
            updateForwordStatus(nodeForwordInfo);
        }
        jdbcTemplate.execute(sql);
        String queryNewAduitLogIDSql = "SELECT LAST_INSERT_ID()";
        int auditLogID = jdbcTemplate.queryForObject(queryNewAduitLogIDSql, Integer.class);
        return auditLogID;
    }

    /**
     * @param workflowID
     * @param flowNodeID
     * @param formDataID
     * @param version
     * @param userIDS
     * @param forwordorID
     * @param bEntityInfos
     * @param nodeForwordInfo
     * @throws Exception
     */
    public int insertAuditLog(String workflowID, String flowNodeID, Integer formDataID, Integer version, String userIDS, Integer forwordorID, List<BusinessEntityInfo> bEntityInfos, NodeForwordInfo nodeForwordInfo) throws Exception {
        String userIDSInCond = BaseUtil.toInCondNotSprit(userIDS);
        String sql = String.format("INSERT INTO AuditLog (auditerID,workflowID,flowNodeId,formID,status,forworderID,version) "
                        + "SELECT u.id,'%s','%s',%d,0,%d,%d FROM User u WHERE u.id IN (%s)",
                workflowID, flowNodeID, formDataID, forwordorID, version, userIDSInCond);
        if (bEntityInfos != null) {
            for (BusinessEntityInfo bEntityInfo : bEntityInfos){
                updateBDStatus(bEntityInfo);
            }
        }
        if (nodeForwordInfo != null) {
            updateForwordStatus(nodeForwordInfo);
        }

        jdbcTemplate.execute(sql);
        String queryNewAduitLogIDSql = "SELECT LAST_INSERT_ID()";
        int auditLogID = jdbcTemplate.queryForObject(queryNewAduitLogIDSql, Integer.class);
        return auditLogID;
    }

    /**
     * 更新审核状态
     *
     * @param id
     * @throws Exception
     */
    public void updateAuditLogStatus(int id, int status, String reply, int forwordStatus, NodeForwordInfo nodeForwordInfo, BusinessEntityInfo bEntityInfo) throws Exception {
        String sql = String.format("UPDATE AuditLog SET status=%d,reply='%s',forwordStatus=%d,auditTime=now() WHERE id=%d",
                status, reply, forwordStatus, id);
        if (nodeForwordInfo != null) {
            updateForwordStatus(nodeForwordInfo);
        }
        if (bEntityInfo != null){
            updateBDStatus(bEntityInfo);
        }
        jdbcTemplate.execute(sql);
    }

    /**
     * 审核失败
     *
     * @param id
     * @param status
     * @param reply
     * @param forwordStatus
     * @param flowNodeID
     * @param formDataID
     * @param version
     * @param bEntityInfo
     * @throws Exception
     */
    public void updateAuditLogStatus(int id, int status, String reply, int forwordStatus, String flowNodeID, Integer formDataID, Integer version, BusinessEntityInfo bEntityInfo) throws Exception {
        String sql = String.format("UPDATE AuditLog SET status=%d,reply='%s',forwordStatus=%d,auditTime=now() WHERE id=%d",
                status, reply, forwordStatus, id);
        String sql1 = String.format("UPDATE AuditLog SET status=4 WHERE flowNodeId='%s' AND formID='%s' AND version=%d",
                flowNodeID, formDataID, version);

        if (bEntityInfo != null){
            updateBDStatus(bEntityInfo);
        }
        jdbcTemplate.execute(sql1);
        jdbcTemplate.execute(sql);

    }

    /**
     * 根据流程节点ID和业务数据ID获取当前节点下业务数据未审核通过的数量
     *
     * @param flowNodeID
     * @param bdID
     * @return
     * @throws Exception
     */
    public int queryAuditedCount(String flowNodeID, Integer bdID, Integer version) throws Exception {
        String sql = String.format("SELECT COUNT(*) FROM AuditLog al WHERE flowNodeId='%s' AND formID=%d AND version=%d  ",
                flowNodeID, bdID, version);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    /**
     * 获取指定节点和提交版本，业务表单流转的数量
     *
     * @param nodeDBName
     * @param flowNodeID
     * @param bdID
     * @param version
     * @return
     * @throws Exception
     */
    public int queryForwordCount(String nodeDBName, String flowNodeID, Integer bdID, Integer version) throws Exception {
        String sql = String.format("SELECT COUNT(*) FROM %s  WHERE flowNodeId='%s' AND formID=%d AND version=%d ",
                nodeDBName, flowNodeID, bdID, version);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    /**
     * 针对开始节点并且转交节点是审核节点的情况
     *
     * @param flowNodeID
     * @param bdID
     * @return
     * @throws Exception
     */
    public int queryForwordCountForBegin(String flowNodeID, Integer bdID) throws Exception {
        String sql = String.format("SELECT COUNT(*) FROM AuditLog  WHERE flowNodeId='%s' AND formID=%d  AND status<>4 AND status<>2 ",
                flowNodeID, bdID);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    /**
     * 更新审核记录转发状态
     *
     * @param nfi
     * @throws Exception
     */
    public void updateForwordStatus(NodeForwordInfo nfi) throws Exception {

        String sql = String.format("UPDATE %s SET forwordStatus=%d  WHERE flowNodeId='%s' AND formID='%s' AND version='%s'",
                nfi.getFlowNodeEntityName(), nfi.getForwordStatus(), nfi.getFlowNodeID(), nfi.getFormDataID(), nfi.getVersion());
        jdbcTemplate.execute(sql);
    }


    /*--------------------------------------------------------------------*/

    /*---------------------------维修指派记录操作-----------------------------*/

    /**
     * 关联用户插入指派记录
     *
     * @param flowNodeID
     * @param formID
     * @param userIDS
     * @throws Exception
     */
    public void insertDispatchLog(String workFlowID, String flowNodeID, Integer formID, String userIDS, Integer forwordorID,
                                  BusinessEntityInfo bEntityInfo, NodeForwordInfo nodeForwordInfo) throws Exception {
        String userIDSInCond = BaseUtil.toInCondNotSprit(userIDS);
        String sql = String.format("INSERT INTO RepairDispatchLog (ordersID,workFlowID,flowNodeId,formID,forwordorID) "
                        + "SELECT u.id,'%s','%s',%d,%d FROM User u WHERE u.id IN (%s)"
                , workFlowID, flowNodeID, formID, forwordorID, userIDSInCond);
        if (bEntityInfo != null) {
            updateBDStatus(bEntityInfo);
        }
        if (nodeForwordInfo != null) {
            updateForwordStatus(nodeForwordInfo);
        }
        jdbcTemplate.execute(sql);

    }

    /**
     * 根据流程节点ID和业务数据ID获取当前节点下业务数据未指派的数量
     *
     * @param flowNodeID
     * @param bdID
     * @param status
     * @return
     * @throws Exception
     */
    public int queryDispathedCount(String flowNodeID, String bdID, int status) throws Exception {
        String sql = String.format("SELECT COUNT(*) FROM RepairDispatchLog rdl WHERE flowNodeId='%s' AND formID='%s' AND status<>%d",
                flowNodeID, bdID, status);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    /**
     * 更新指派状态
     *
     * @param ordersID
     * @param workFlowID
     * @param flowNodeID
     * @param formDataID
     * @param status
     * @throws Exception
     */
    public void updateDispathedStatus(Integer ordersID, String workFlowID, String flowNodeID, Integer formDataID, int status) throws Exception {
        String sql = String.format("UPDATE RepairDispatchLog SET status=%d,dispatchTime=now() WHERE "
                        + "workFlowID='%s' AND flowNodeId='%s' AND formID=%d AND ordersID=%d",
                status, workFlowID, flowNodeID, formDataID, ordersID);
        jdbcTemplate.execute(sql);
    }

    /*--------------------------------------------------------------------*/

    /*---------------------------维修处理记录操作-----------------------------*/

    /**
     * 插入维修处理记录
     *
     * @param workFlowID
     * @param flowNodeID
     * @param dispatchNodeID
     * @param formDataID
     * @param ordersID
     * @param userIDS
     * @param forwordID
     * @param bEntityInfo
     * @param nodeForwordInfo
     * @throws Exception
     */
    public void insertProcLog(String workFlowID, String flowNodeID, String dispatchNodeID, Integer formDataID, Integer ordersID, String userIDS, String forwordID,
                              BusinessEntityInfo bEntityInfo, NodeForwordInfo nodeForwordInfo) throws Exception {
        String userIDSInCond = BaseUtil.toInCondNotSprit(userIDS);
        String sql = String.format("INSERT INTO RepairProcLog (ordersID,processerID,workFlowID,flowNodeId,formID,status,forwordStatus) "
                        + "SELECT %d,u.id,'%s','%s',%d,0,'%s' FROM User u WHERE u.id IN (%s)"
                , ordersID, workFlowID, flowNodeID, formDataID, forwordID, userIDSInCond);
        if (bEntityInfo != null) {
            updateBDStatus(bEntityInfo);
        }
        if (nodeForwordInfo != null) {
            updateForwordStatus(nodeForwordInfo);
        }
        //更新报修指派记录的指派状态
        updateDispathedStatus(ordersID, workFlowID, dispatchNodeID, formDataID, 1);
        jdbcTemplate.execute(sql);

    }

    /**
     * 道路巡检维修处理记录插入
     *
     * @param workFlowID
     * @param flowNodeID
     * @param dispatchNodeID
     * @param formDataID
     * @param ordersID
     * @param userIDS
     * @param version
     * @param forwordID
     * @param bEntityInfo
     * @param nodeForwordInfo
     * @return
     * @throws Exception
     */
    public int insertProcLogForDLYH(String workFlowID, String flowNodeID, String dispatchNodeID, Integer formDataID, Integer ordersID, String userIDS, Integer version, String forwordID,
                                    BusinessEntityInfo bEntityInfo, NodeForwordInfo nodeForwordInfo) throws Exception {
        String userIDSInCond = BaseUtil.toInCondNotSprit(userIDS);
        String sql = String.format("INSERT INTO RepairProcLog (ordersID,processerID,ordersOrgID,procOrgID,workFlowID,flowNodeId,formID,status,forwordStatus,version) "
                        + "SELECT %d,processer.id,ordersOrg.id,procOrg.id,'%s','%s',%d,1,'%s',%d FROM User processer,User orders,Org procOrg,Org ordersOrg WHERE processer.orgIds=procOrg.id "
                        + "AND orders.orgIds=ordersOrg.id AND orders.id=%d AND processer.id IN (%s) "
                , ordersID, workFlowID, flowNodeID, formDataID, forwordID, version, ordersID, userIDSInCond);
        if (bEntityInfo != null) {
            updateBDStatus(bEntityInfo);
        }
        if (nodeForwordInfo != null) {
            updateForwordStatus(nodeForwordInfo);
        }
        jdbcTemplate.execute(sql);
        String queryNewAduitLogIDSql = "SELECT LAST_INSERT_ID()";
        int procLogID = jdbcTemplate.queryForObject(queryNewAduitLogIDSql, Integer.class);
        return procLogID;

    }


    /**
     * 获取参与该维修处理任务的不在某种状态的用户数量
     *
     * @param flowNodeID
     * @param bdID
     * @param status
     * @return
     * @throws Exception
     */
    public int queryProcCount(String flowNodeID, Integer bdID, int status) throws Exception {
        String sql = String.format("SELECT COUNT(*) FROM RepairProcLog rpl WHERE flowNodeId='%s' AND formID=%d AND status<>%d",
                flowNodeID, bdID, status);
        return jdbcTemplate.queryForObject(sql, Integer.class);
    }

    /**
     * 更新维修处理状态
     *
     * @param id
     * @param status
     * @param forwordStatus
     * @param bEntityInfo
     * @throws Exception
     */
    public void updateProcStatus(int id, int status, int forwordStatus, BusinessEntityInfo bEntityInfo) throws Exception {
        String sql = String.format("UPDATE RepairProcLog SET status=%d,forwordStatus=%d,reciveTime=now() WHERE id=%d",
                status, forwordStatus, id);
        jdbcTemplate.execute(sql);

        if (bEntityInfo != null) {
            updateBDStatus(bEntityInfo);
        }
    }

    public void updateProcStatus(int id, int status, int forwordStatus, BusinessEntityInfo bEntityInfo, NodeForwordInfo nodeForwordInfo) throws Exception {
        String sql = String.format("UPDATE RepairProcLog SET status=%d,forwordStatus=%d,reciveTime=now() WHERE id=%d",
                status, forwordStatus, id);
        jdbcTemplate.execute(sql);
        if (bEntityInfo != null) {
            updateBDStatus(bEntityInfo);
        }
        if (nodeForwordInfo != null) {
            updateForwordStatus(nodeForwordInfo);
        }
    }

    /*--------------------------------------------------------------------*/

    /*----------------------------业务数据操作-------------------------------*/

    /**
     * 更新业务数据状态
     *
     * @param bEntityInfo
     * @throws Exception
     */
    public void updateBDStatus(BusinessEntityInfo bEntityInfo) throws Exception {
		/*String sql=String.format("UPDATE %s SET %s=%d,%s=%d ",
				bEntityInfo.getEntityName(),
				bEntityInfo.getbStatusKey(),bEntityInfo.getbStatusVal(),
				bEntityInfo.getaStatusKey(),bEntityInfo.getaStatusVal());*/
        StringBuffer sql = new StringBuffer("UPDATE " + bEntityInfo.getEntityName() + " SET ");

        if (bEntityInfo.getbStatusKey() != null) {
            sql.append(bEntityInfo.getbStatusKey() + " = " + bEntityInfo.getbStatusVal() + ",");
        }

        if (bEntityInfo.getCondition() != null) {
            Map<String, String> condition = bEntityInfo.getCondition();
            Set<String> keys = condition.keySet();
            for (String key : keys) {
                sql.append(key + "=" + condition.get(key) + ",");
            }
        }
        sql = sql.deleteCharAt(sql.lastIndexOf(","));
        sql.append(" WHERE " + bEntityInfo.getIdKey() + "=" + bEntityInfo.getIdVal());
        jdbcTemplate.execute(sql.toString());

        //WHERE %s='%s'
    }

    /**
     * 获取业务数据
     *
     * @param bEntityInfo
     * @throws Exception
     */
    public Map<String, Object> queryBD(BusinessEntityInfo bEntityInfo) throws Exception {
        String sql = String.format("SELECT * FROM  %s WHERE  %s='%s'",
                bEntityInfo.getEntityName(),
                bEntityInfo.getIdKey(), bEntityInfo.getIdVal());
        return jdbcTemplate.queryForMap(sql);
    }

    /**
     * 获取业务数据
     *
     * @param entityName
     * @throws Exception
     */
    public Map<String, Object> queryBD(String entityName, String idKey, String idVal) throws Exception {
        String sql = String.format("SELECT * FROM  %s WHERE  %s='%s'",
                entityName,
                idKey, idVal);
        return jdbcTemplate.queryForMap(sql);
    }
    /*--------------------------------------------------------------------*/

    /*-------------------------------节点用户操作----------------------------------*/

    /**
     * 根据机构角色和流程节点获取关联的节点机构角色用户
     *
     * @param flowNodeID
     * @param orgIDS
     * @param roleIDS
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryNodeOrgRoleUsers(String flowNodeID,
                                                           String orgIDS,
                                                           String roleIDS,
                                                           String projectIds,
                                                           String sectionIds,
                                                           String siteIds,
                                                           PageInfo pageInfo) throws Exception {
        List<Object> args=new ArrayList<Object>();
        StringBuffer sql = new StringBuffer("SELECT ");
        sql.append("u.id userID,u.name userName,u.sex userSex,");
        sql.append("o.id orgID,o.name orgName,o.orderNo orderNo,");
        sql.append("r.id roleID,r.name roleName,COUNT(*) size ");
        sql.append("FROM user u,user_role ur,nodeorgrole nor,department o,role r,user_org uo ");

        if(BaseUtil.isNotEmpty(siteIds)) {
            sql.append(",org_site os ");
        }
        sql.append("WHERE uo.userId=u.id ");
        sql.append("AND u.status<>0 ");
        sql.append("AND ur.userId=u.id AND nor.roleId=ur.roleId ");
        sql.append("AND o.id=uo.orgId AND nor.orgID=o.id AND nor.roleID=r.id ");
        sql.append("AND nor.flowNodeId=?  ");
        args.add(flowNodeID);
        if (BaseUtil.isNotEmpty(siteIds)) {
            sql.append("AND os.orgId=o.id ");
        }

        if (roleIDS != null && !roleIDS.equals("")) {
            roleIDS = BaseUtil.toInCondNotSprit(roleIDS);
            sql.append(" AND r.id IN  ");
            args.addAll(BaseUtil.toInConds(sql,roleIDS));
        }
        if (orgIDS != null && !orgIDS.equals("")) {
            orgIDS = BaseUtil.toInCondNotSprit(orgIDS);
            sql.append(" AND u.departmentID IN  ");
            args.addAll(BaseUtil.toInConds(sql,orgIDS));
        }
        if (BaseUtil.isNotEmpty(projectIds)) {
            sql.append(" AND ups.projectId IN  ");
            args.addAll(BaseUtil.toInConds(sql,projectIds));
        }

        if (BaseUtil.isNotEmpty(siteIds)) {
            sql.append(" AND os.siteId IN  ");
            args.addAll(BaseUtil.toInConds(sql,siteIds));
        }
        sql.append(" GROUP BY u.id,r.id,o.id ");

        List<Map<String,Object>> result=null;
        if (pageInfo != null
                && pageInfo.getCurPage() != null && pageInfo.getCurPage() != 0
                && pageInfo.getPageSize() != null && pageInfo.getPageSize() != 0) {
            sql.append("LIMIT " + pageInfo.getStartRow() + "," + pageInfo.getPageSize());
            result=jdbcTemplate.queryForList(sql.toString(),args.toArray());
            String countSql = "SELECT FOUND_ROWS() FROM DUAL";
            pageInfo.setTotalRows(jdbcTemplate.queryForObject(countSql, Integer.class));
            pageInfo.createTotalPages();
        }else{
            result=jdbcTemplate.queryForList(sql.toString(),args.toArray());
        }
        return result;
    }

    /**
     * 根据机构角色和流程节点获取关联的节点机构用户
     *
     * @param flowNodeId
     * @param orgIds
     * @param roleIds
     * @param pageInfo
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryNodeOrgUsers(String flowNodeId, String orgIds, String roleIds,
                                                       String projectIds,String sectionIds,String siteIds, PageInfo pageInfo) throws Exception {

        List<Object> args=new ArrayList<Object>();
        StringBuffer sql = new StringBuffer("SELECT SQL_CALC_FOUND_ROWS ");
        sql.append("u.id userID,u.name userName,u.sex userSex,");
        sql.append("o.id orgID,o.name orgName,o.orderNo orderNo,");
        sql.append("r.id roleID,r.name roleName,COUNT(*) size ");
        sql.append("FROM User u,User_Role ur,NodeOrgRole nor,Department o,Role r, user_org uo ");
        if (BaseUtil.isNotEmpty(siteIds)) {
            sql.append(",org_site os ");
        }
        sql.append("WHERE u.id=ur.userID AND r.id=ur.roleID ");

        sql.append("AND u.status<>0 ");
        sql.append("AND nor.orgID=o.id ");
        sql.append("AND nor.flowNodeId=? ");
        args.add(flowNodeId);
        sql.append(" AND uo.userId=u.id ");
        sql.append(" AND o.id=uo.orgId ");

        if (BaseUtil.isNotEmpty(siteIds)) {
            sql.append("AND os.orgId=o.id ");
        }
        if (BaseUtil.isNotEmpty(roleIds)) {
            roleIds = BaseUtil.toInCondNotSprit(roleIds);
            sql.append(" AND ur.roleID IN  ");
            args.addAll(BaseUtil.toInConds(sql,roleIds));
        }
        if (BaseUtil.isNotEmpty(orgIds)) {
            orgIds = BaseUtil.toInCondNotSprit(orgIds);
            sql.append(" AND uo.orgId IN  ");
            args.addAll(BaseUtil.toInConds(sql,orgIds ));
        }
        if (BaseUtil.isNotEmpty(projectIds)) {
            sql.append(" AND pur.projectIds IN  ");
            args.addAll(BaseUtil.toInConds(sql,projectIds));
        }
        if (BaseUtil.isNotEmpty(siteIds)) {
            sql.append(" AND os.siteId IN  ");
            args.addAll(BaseUtil.toInConds(sql,siteIds));
        }
        sql.append(" GROUP BY u.id,r.id,o.id ");
        List<Map<String,Object>> result=null;
        if (pageInfo != null
                && pageInfo.getCurPage() != null && pageInfo.getCurPage() != 0
                && pageInfo.getPageSize() != null && pageInfo.getPageSize() != 0) {
            sql.append("LIMIT " + pageInfo.getStartRow() + "," + pageInfo.getPageSize());
            result=jdbcTemplate.queryForList(sql.toString(),args.toArray());
            String countSql = "SELECT FOUND_ROWS() FROM DUAL";
            pageInfo.setTotalRows(jdbcTemplate.queryForObject(countSql, Integer.class));
            pageInfo.createTotalPages();
        }else{
            result=jdbcTemplate.queryForList(sql.toString(),args.toArray());
        }
        return result;
    }

    /**
     * 查询节点用户关联的用户信息
     *
     * @param flowNodeId
     * @param orgIds
     * @param pageInfo
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryNodeUsers(String flowNodeId,
                                                    String orgIds,
                                                    String roleIds,
                                                    String projectIds,
                                                    String sectionIds,
                                                    String siteIds,
                                                    PageInfo pageInfo) throws Exception {

        StringBuffer sql = new StringBuffer("SELECT ");
        sql.append("u.id userID,u.name userName,u.sex userSex,");
        sql.append("o.id orgID,o.name orgName,o.orderNo orderNo,");
        sql.append("r.id roleID,r.name roleName,COUNT(*) size ");
        sql.append("FROM user u,user_role ur,nodeuser nu,Department o,role r,user_org uo ");

        List<Object> args=new ArrayList<Object>();
        if (BaseUtil.isNotEmpty(siteIds)) {
            sql.append(",org_site os ");
        }
        sql.append(" WHERE u.id=ur.userID AND r.id=ur.roleID ");

        if (BaseUtil.isNotEmpty(siteIds)) {
            sql.append("AND os.orgId=o.id ");
        }
        sql.append("AND u.status<>0 ");
        sql.append("AND o.id=uo.orgId AND uo.userID=u.id ");
        sql.append("AND nu.userID=u.id ");
        sql.append("AND nu.flowNodeId=? ");
        args.add(flowNodeId);

        if (roleIds != null && !roleIds.equals("")) {
            roleIds = BaseUtil.toInCondNotSprit(roleIds);
            sql.append(" AND ur.roleID IN  ");
            args.addAll(BaseUtil.toInConds(sql,roleIds));
        }
        if (orgIds != null && !orgIds.equals("")) {
            orgIds = BaseUtil.toInCondNotSprit(orgIds);
            sql.append(" AND uo.orgId IN  ");
            args.addAll(BaseUtil.toInConds(sql,orgIds));
        }
        if (BaseUtil.isNotEmpty(projectIds)) {
            sql.append(" AND pur.projectIds IN  ");
            args.addAll(BaseUtil.toInConds(sql, projectIds));
        }

        if (BaseUtil.isNotEmpty(siteIds)) {
            sql.append(" AND os.siteId IN  ");
            args.addAll(BaseUtil.toInConds(sql,siteIds));
        }
        sql.append(" GROUP BY u.id,r.id,o.id ");
        List<Map<String,Object>> result=null;

        if (pageInfo != null
                && pageInfo.getCurPage() != null && pageInfo.getCurPage() != 0
                && pageInfo.getPageSize() != null && pageInfo.getPageSize() != 0) {
            sql.append("LIMIT " + pageInfo.getStartRow() + "," + pageInfo.getPageSize());
            result=jdbcTemplate.queryForList(sql.toString(),args.toArray());
            String countSql = "SELECT FOUND_ROWS() FROM DUAL";
            pageInfo.setTotalRows(jdbcTemplate.queryForObject(countSql, Integer.class));
            pageInfo.createTotalPages();
        }else{
            result=jdbcTemplate.queryForList(sql.toString(),args.toArray());
        }
        return result;
    }

    /**
     * 查询当前操作用户关联的范围级机构角色用户信息
     *
     * @param flowNodeID
     * @param scopeOrgIDS
     * @param orgIDS
     * @param pageInfo
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryNodeScopeOrgRoleUsers(String flowNodeID,
                                                                String scopeOrgIDS,
                                                                String orgIDS,
                                                                String roleIDS,
                                                                String projectIds,
                                                                String sectionIds,
                                                                PageInfo pageInfo
                                                                ) throws Exception {
        scopeOrgIDS = BaseUtil.toInCondNotSprit(scopeOrgIDS);
        StringBuffer sql = new StringBuffer("SELECT ");
        sql.append("u.id userID,u.name userName,u.sex userSex,");
        sql.append("o.id orgID,o.name orgName,o.orderNo orderNo,");
        sql.append("r.id roleID,r.name roleName,COUNT(*) size ");
        sql.append("FROM User u,User_Role ur,Department o,Role r,NodeOrgRole nor,user_org uo ");
        if(BaseUtil.isNotEmpty(sectionIds)){
            sql.append(",user_project_section ups ");
        }
        sql.append("WHERE u.id=ur.userID AND r.id=ur.roleID AND nor.roleID=r.id ");
        if(BaseUtil.isNotEmpty(sectionIds)){
            sql.append(" AND ups.userId=u.id ");
        }
        sql.append("AND uo.orgId=o.id AND uo.userId=u.id ");
        sql.append("AND u.status<>0 ");
        sql.append("AND uo.orgId IN (" + scopeOrgIDS + ") AND nor.flowNodeId =" + flowNodeID + " ");

        if (roleIDS != null && !roleIDS.equals("")) {
            roleIDS = BaseUtil.toInCondNotSprit(roleIDS);
            sql.append(" AND ur.roleID IN (" + roleIDS + ")");
        }
        if (orgIDS != null && !orgIDS.equals("")) {
            orgIDS = BaseUtil.toInCondNotSprit(orgIDS);
            sql.append(" AND uo.orgId IN (" + orgIDS + ") ");
        }
        if(BaseUtil.isNotEmpty(sectionIds)){
            sql.append(" AND ups.sectionId IN ("+sectionIds+") ");
        }
        sql.append(" GROUP BY u.id,r.id,o.id ");
        List<Map<String,Object>> result=jdbcTemplate.queryForList(sql.toString());
        if (pageInfo != null
                && pageInfo.getCurPage() != null && pageInfo.getCurPage() != 0
                && pageInfo.getPageSize() != null && pageInfo.getPageSize() != 0) {

            sql.append("LIMIT " + pageInfo.getStartRow() + "," + pageInfo.getPageSize());
            String countSql = "SELECT FOUND_ROWS() FROM DUAL";
            pageInfo.setTotalRows(jdbcTemplate.queryForObject(countSql, Integer.class));
            pageInfo.createTotalPages();
        }
        return result;
    }

    /**
     * 查询当前用户关联的范围级机构用户信息
     *
     * @param scopeOrgIDS
     * @param orgIds
     * @param pageInfo
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryNodeScopeOrgUsers(String scopeOrgIDS,
                                                            String orgIds,
                                                            String roleIds,
                                                            String projectIds,
                                                            String sectionIds,
                                                            PageInfo pageInfo) throws Exception {

        scopeOrgIDS = BaseUtil.toInCondNotSprit(scopeOrgIDS);
        StringBuffer sql = new StringBuffer("SELECT SQL_CALC_FOUND_ROWS "
                + "u.id userID,u.name userName,u.sex userSex,"
                + "o.id orgID,o.name orgName,o.orderNo orderNo,"
                + "r.id roleID,r.name roleName,COUNT(*) size "
                + "FROM User u,User_Role ur,Department o,Role r,user_org uo ");

        if (BaseUtil.isNotEmpty(sectionIds)) {
            sql.append(",user_project_section ups ");
        }
        sql.append("WHERE u.id=ur.userID AND r.id=ur.roleID  AND uo.userId=user.id ");
        sql.append("AND u.status<>0 ");
        sql.append("AND o.id=uo.orgId AND o.id IN (" + scopeOrgIDS + ") ");

        if (BaseUtil.isNotEmpty(sectionIds)) {
            sql.append("AND ups.userId=u.id ");
        }
        if (roleIds != null && !roleIds.equals("")) {
            roleIds = BaseUtil.toInCondNotSprit(roleIds);
            sql.append(" AND ur.roleID IN (" + roleIds + ") ");
        }
        if (orgIds != null && !orgIds.equals("")) {
            orgIds = BaseUtil.toInCondNotSprit(orgIds);
            sql.append(" AND o.id IN (" + orgIds + ") ");
        }
        if (BaseUtil.isNotEmpty(sectionIds)) {
            sql.append(" AND ups.sectionID IN (" + sectionIds + ") ");
        }
        sql.append(" GROUP BY u.id,r.id,o.id ");
        List<Map<String,Object>> result=jdbcTemplate.queryForList(sql.toString());
        if (pageInfo != null && pageInfo.getCurPage() != null && pageInfo.getCurPage() != 0
                && pageInfo.getPageSize() != null && pageInfo.getPageSize() != 0) {
            sql.append("LIMIT " + pageInfo.getStartRow() + "," + pageInfo.getPageSize());
            String countSql = "SELECT FOUND_ROWS() FROM DUAL";
            pageInfo.setTotalRows(jdbcTemplate.queryForObject(countSql, Integer.class));
            pageInfo.createTotalPages();
        }

        return result;
    }
    /*--------------------------------------------------------------------------*/

    /*-------------------------------业务流程进度查询----------------------------------*/

    /**
     * 获取业务数据的流程进度
     *
     * @param formDataID
     * @return
     * @throws Exception
     */
    public List<Map<String, Object>> queryWorkProgress(String workFlowIDS, String formDataID, PageInfo pageInfo) throws Exception {
        String sql = String.format("CALL proc_QueryWorkProgress(\"%s\",'%s','%d','%d')", workFlowIDS, formDataID, pageInfo.getStartRow(), pageInfo.getPageSize());
        List<Map<String, Object>> workProgress = jdbcTemplate.queryForList(sql);
        if (workProgress != null && workProgress.size() > 0) {
            pageInfo.setTotalRows(Integer.parseInt(workProgress.get(0).get("rowSize").toString()));
        }
        pageInfo.createTotalPages();
        return workProgress;
    }

    public List<Map<String, Object>> getWorkProgress(String workFlowId, Integer bId, Integer order, PageInfo pageInfo) throws Exception {
        String flowNodeIds = "";
        if (StrUtil.isBlank(workFlowId)){
            throw new SystemException("参数 workFlowId 不能为空");
        }
        if (bId==null){
            throw new SystemException("参数 bId 不能为空");
        }
        if ("1".equals(workFlowId)) {
            flowNodeIds = "1,2,3,4,5,6,11";
        }

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM ( ")
                .append("SELECT ")
                .append("t1.id AS tID,")
                .append("t1.flowNodeID,")
                .append("t1.status AS tStatus,")
                .append("t1.forwarderID,")
                .append("t1.processerID,")
                .append("DATE_FORMAT(t1.createTime,'%Y-%m-%d %H:%i:%s') reviceTime,")
                .append("DATE_FORMAT(t1.procTime,'%Y-%m-%d %H:%i:%s') procTime ")
                .append("FROM tasklog t1 WHERE t1.formID=? AND t1.flowNodeID IN ( "+flowNodeIds+" ) ")
                .append("UNION ALL ")
                .append("SELECT ")
                .append("ht.tID,")
                .append("ht.flowNodeID,")
                .append("ht.status AS tStatus,")
                .append("ht.forwarderID,")
                .append("ht.processerID,")
                .append("DATE_FORMAT(ht.taskLogCreateTime,'%Y-%m-%d %H:%i:%s') reviceTime,")
                .append("DATE_FORMAT(ht.procTime,'%Y-%m-%d %H:%i:%s') procTime ")
                .append("FROM historytasklog ht ")
                .append("LEFT JOIN tasklog t2 ON t2.id=ht.tID WHERE ht.bID=? ")
                .append(" AND ht.flowNodeID IN ( "+flowNodeIds+" )")
                .append(" ) tab ");
        if (order != null && order == 0){
            sql.append(" ORDER BY tID ASC ");
        }else {
            sql.append(" ORDER BY tID DESC ");
        }

        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> listWorkProgressMaps = BaseUtil.getData(sql, pageInfo, workFlowDao, bId, bId);
        StringBuilder flowNodeIDs = new StringBuilder();
        StringBuilder forwarderIDs = new StringBuilder();
        StringBuilder processerIDs = new StringBuilder();

        for (Map<String,Object> map: listWorkProgressMaps) {
            flowNodeIDs.append(String.valueOf(map.get("flowNodeID"))).append(",");
            forwarderIDs.append(String.valueOf(map.get("forwarderID"))).append(",");
            processerIDs.append(String.valueOf(map.get("processerID"))).append(",");
        }
        // 获取主服务流程节点及流程节点用户操作信息
        List<Map<String, Object>> flowNodes= queryFlowNodesByID(flowNodeIDs.substring(0,flowNodeIDs.toString().length()-1));
        List<Map<String, Object>> forwarders = userService.getUsersByIds(forwarderIDs.substring(0,forwarderIDs.toString().length()-1));
        List<Map<String, Object>> processers = userService.getUsersByIds(processerIDs.substring(0,processerIDs.toString().length()-1));
        // 数据对象转化成JSONArray
        JSONArray flowNodeArray = JSONArray.fromObject(flowNodes);
        JSONArray forwarderArray = JSONArray.fromObject(forwarders);
        JSONArray processerArray = JSONArray.fromObject(processers);
        for (Map<String,Object> map: listWorkProgressMaps) {
            // 流程节点名称
            for(int i=0;i<flowNodeArray.size();i++) {
                JSONObject flowNodeObj = flowNodeArray.getJSONObject(i);
                if (map.get("flowNodeID").equals(flowNodeObj.get("id"))) {
                    map.put("flowNodeName", flowNodeObj.get("name"));
                    break;
                }

            }
            // 转交人用户名称
            for(int i=0;i<forwarderArray.size();i++) {
                JSONObject forwarderObj = forwarderArray.getJSONObject(i);
                if (map.get("forwarderID").equals(forwarderObj.get("id"))) {
                    map.put("forwarderName", forwarderObj.get("name"));
                    break;
                }
            }
            // 处理人用户名称
            for(int i=0;i<processerArray.size();i++) {
                JSONObject processerObj = processerArray.getJSONObject(i);
                if (map.get("processerID").equals(processerObj.get("id"))) {
                    map.put("processerName", processerObj.get("name"));
                    break;
                }
            }
            result.add(map);
        }

        return result;
    }

    public List<Map<String, Object>> getWorkFlowLogs(HttpServletRequest request, String workFlowId, Integer bId, PageInfo pageInfo) throws Exception {
        Map<String, Object> condition = this.getCondition(request);
        StringBuilder sql = new StringBuilder();
        // 设备故障上报及维修
        if ("1".equals(workFlowId)) {
            sql.append("SELECT dfrp.bId,");
            sql.append(" dfrp.tId,");
            sql.append(" dfrp.workFlowId,");
            sql.append(" dfrp.workFlowName,");
            sql.append(" dfrp.flowNodeId,");
            sql.append(" dfrp.flowNodeName,");
            sql.append(" dfrp.pCode,");
            sql.append(" dfrp.processerId,");
            sql.append(" dfrp.processerName,");
            sql.append(" dfrp.detail,");
            sql.append(" dfrp.fileNames,");
            sql.append(" dfrp.label,");
            sql.append(" DATE_FORMAT(dfrp.createTime,'%Y-%m-%d %H:%i:%s') As createTime,");
            sql.append(" dfrp.repairProgress ");
            sql.append(" FROM devicefaultreportandrepairprocessrecord dfrp WHERE 1=1 ");
        } else if ("2".equals(workFlowId)) {
            // 养护巡检计划
            sql.append("SELECT ipr.* FROM improcessrecord ipr WHERE 1=1 ");
        }

        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();
        List<Map<String, Object>> listWorkFlowLogMaps;
        if (StrUtil.isNotBlank(request.getParameter("order"))&&"0".equals(request.getParameter("order"))){
            listWorkFlowLogMaps =  BaseUtil.getData(sql, condition, pageInfo, workFlowDao, "id", true);
        }else{
            listWorkFlowLogMaps = BaseUtil.getData(sql, condition, pageInfo, workFlowDao, "id", false);
        }

        StringBuilder processerIDs = new StringBuilder();
        for (Map<String,Object> map: listWorkFlowLogMaps) {
            processerIDs.append(String.valueOf(map.get("processerId"))).append(",");
        }

        // 获取主服务流程节点用户操作信息
        List<Map<String, Object>> processerNams = userService.getUsersByIds(processerIDs.substring(0,processerIDs.toString().length()-1));
        // 数据对象转化成JSONArray
        JSONArray processerArray = JSONArray.fromObject(processerNams);
        for (Map<String,Object> map: listWorkFlowLogMaps) {
            // 处理人用户名称
            for(int i=0;i<processerArray.size();i++) {
                JSONObject processerObj = processerArray.getJSONObject(i);
                if (map.get("processerId").equals(processerObj.get("id"))) {
                    if (BaseUtil.isNotEmpty(processerObj.get("headerpath"))) {
                        map.put("headerpath", processerObj.get("headerpath"));
                    }
                    map.put("processerName", processerObj.get("name"));
                    break;
                }
            }
            result.add(map);
        }
        return result;
    }

    private Map<String, Object> getCondition(HttpServletRequest request) throws Exception {
        Map<String, Object> condition = new HashMap<>();
        if (StrUtil.isNotBlank(request.getParameter("tId"))){
            condition.put(" AND tId=? ", Integer.valueOf(request.getParameter("tId")));
        }
        if (StrUtil.isNotBlank(request.getParameter("bId"))){
            condition.put(" AND bId=? ", Integer.valueOf(request.getParameter("bId")));
        }
        if (StrUtil.isNotBlank(request.getParameter("status"))){
            condition.put(" AND status IN ("+BaseUtil.getPlaceholder(request.getParameter("status"))+") ", BaseUtil.getArgs(request.getParameter("status"), Integer.class));
        }
        if (StrUtil.isNotBlank(request.getParameter("workFlowId"))){
            condition.put(" AND workFlowId IN ("+BaseUtil.getPlaceholder(request.getParameter("workFlowId"))+") ", BaseUtil.getArgs(request.getParameter("workFlowId"), Integer.class));
        }
        return condition;
    }

    /*--------------------------------------------------------------------------*/


    /*-------------------------------节点机构操作----------------------------------*/

    /**
     * 根据机构ID获取机构信息
     *
     * @return
     * @throws Exception
     */
    public Department queryOrgByID(Integer orgID) throws Exception {
        Map<String, String> condition = new HashMap<String, String>();
        condition.put("id", "=" + orgID);
        return departmentDao.queryOnlyEntity(condition);
    }
    /*--------------------------------------------------------------------------*/

    /*-------------------------------判断表单是否能转交----------------------------------*/

    public boolean isForword(Integer formID, String flowNodeID, Map<String, String> condition) throws Exception {
        String sql = String.format("SELECT COUNT(*) FROM TaskLog "
                        + "WHERE formID='%s' AND flowNodeId='%s' "
                        + "AND status<>2 AND status<>3 ",
                //+ "AND version=(SELECT MAX(version) FROM TaskLog WHERE formID=%s AND flowNodeId='%s') ",
                formID, flowNodeID/*,formID,flowNodeId*/);
        if (condition != null) {
            Set<String> keys = condition.keySet();
            for (String key : keys) {
                sql += " AND " + key + condition.get(key);
            }
        }
        int count = jdbcTemplate.queryForObject(sql, Integer.class);
        boolean isForword = count == 0 ? true : false;
        return isForword;

    }
    /*--------------------------------------------------------------------------*/

    /*-------------------------------获取表单某一流程的当前版本号----------------------------------*/
    public int queryCurVersion(String entityName, String flowNodeID, Integer formID) throws Exception {
        String sql = String.format("SELECT IFNULL(MAX(version),0) FROM %s temp WHERE "
                        + " flowNodeId='%s' AND formID=%d ",
                entityName, flowNodeID, formID);
        int maxVersion = jdbcTemplate.queryForObject(sql, Integer.class);
        return maxVersion;
    }

    /*--------------------------------------------------------------------------*/
    /*-------------------------------获取表单某一流程的当前版本号----------------------------------*/
    public List<Integer> executeTaskSql(List<Map<String, Object>> nodeConfs, Integer formID, String flowNodeID, Integer version) throws Exception {
        List<Integer> bLogIDS = new ArrayList<Integer>();
        for (Map nodeConf : nodeConfs) {
            if (nodeConf.get("isForwordControl") != null && (boolean) nodeConf.get("isForwordControl")) {
                boolean isForword = true;
                Map<String, String> condition = new HashMap<String, String>();
                condition.put("status", "=0");//不存在未接收处理
                condition.put("version", "=" + version);
                isForword = isForword(formID, flowNodeID, condition);
                condition.clear();
                condition.put("status", "=2");//不存在未审核通过
                condition.put("version", "=" + version);
                isForword = isForword(formID, flowNodeID, condition);
                condition.clear();
                condition.put("status", "=3");//不存在会签操作时某一个审核失败而导致的其它待审核记录
                condition.put("version", "=" + version);
                isForword = isForword(formID, flowNodeID, condition);
                //如果不满足转交条件则跳过该节点配置操作
                if (!isForword) {
                    continue;
                }
            }

            if (nodeConf.get("action").equals("add") ||
                    nodeConf.get("action").equals("change")) {
                Integer bLogID = 0;
                if (nodeConf.get("sql") != null) {
//			   Object o = nodeConf.get("sql");
//			   if (o instanceof Map){
//				   Map<String, Object> sqlMap = (Map<String, Object>) o;
//				   List<Object> args = (List<Object>) sqlMap.get("args");
//				   String sql = sqlMap.get("sql").toString();
//				   if (sql.toUpperCase().startsWith("SELECT")){
//					   workFlowDao.getJdbcTemplate().execute(sql);
//				   }else{
//					   workFlowDao.getJdbcTemplate().update(sql, args.toArray());
//				   }
//			   }else{
//				   workFlowDao.getJdbcTemplate().execute(o.toString());
//			   }
                    String sql = nodeConf.get("sql").toString();
                    workFlowDao.getJdbcTemplate().execute(sql);

                    //获取新增业务记录ID
                    if (nodeConf.get("isBLog") != null && (boolean) nodeConf.get("isBLog")) {
                        bLogID = workFlowDao.getJdbcTemplate().queryForObject("SELECT LAST_INSERT_ID()", Integer.class);
                        if (bLogID != null && bLogID != 0) {
                            bLogIDS.add(bLogID);
                        }
                    }
                }

            }
        }
        return bLogIDS;
    }
    /*--------------------------------------------------------------------------*/

    public static void main(String[] args) throws Exception {
        String workFlowJson = "{\"1\":{taskName:\"nodeFlow\",fromLastFormID:\"true\",taskLogID:\"?\",curFlowNodeID:\"?\",flowNodeId:\"?\",formID:\"?\",curVersion:\"?\",userIDS:\"?\"},\"2\":{taskName:\"taskProc\",value:22},\"3\":{taskName:\"taskProc\",value:33}}";
        ObjectMapper objectMapper = new ObjectMapper();
        objectMapper.configure(JsonParser.Feature.ALLOW_UNQUOTED_FIELD_NAMES, true);
        //objectMapper.configure(DeserializationConfig.Feature.ACCEPT_SINGLE_VALUE_AS_ARRAY, true);
        Map<String, Map<String, Object>> workFlowParams = objectMapper.readValue(workFlowJson, Map.class);
        System.out.println(workFlowParams.size());
        for (Map<String, Object> list : workFlowParams.values()) {
            System.out.println(list.toString());
        }
    }

}
